package dao

import com.github.tminglei.slickpg.utils.PlainSQLUtils.nextArrayConverters
import javax.inject.Inject
import models.Tables._
import org.joda.time.DateTime
import org.postgresql.util.HStoreConverter
import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import play.api.libs.json.JsValue
import shared.VarTool
import slick.jdbc.{GetResult, JdbcProfile, PositionedResult}
import tool.Pojo.{PageData, PageQueryData}

import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.Future
import play.api.libs.json._
import utils.MyPostgresProfile.api

import scala.collection.JavaConverters._
import scala.reflect.{api, runtime}
import implicits.Implicits._
import pgSlick.MyPgPositionedResult._

class SampleDao @Inject()(protected val dbConfigProvider: DatabaseConfigProvider) extends
  HasDatabaseConfigProvider[JdbcProfile] {

  //    import profile.api._

  import utils.MyPostgresProfile.api._
  import com.github.tototoshi.slick.MySQLJodaSupport._

  type MyRow = SampleRow
  val table = Sample

  //  def insertOrUpdate(row: SampleRow) = {
  //    val action = {
  //      val delete = table.filter { x =>
  //        x.id === (row.id) && x.userId === row.userId
  //      }.delete
  //      val insert = table += row
  //      delete.flatMap(_ => insert)
  //    }.transactionally
  //    db.run(action)
  //  }

  def insertOrUpdate(row: SampleRow) = {
    val action = {
      val insert = table.insertOrUpdate(row)
      insert
    }.transactionally
    db.run(action)
  }

  def insertAll(rows: List[SampleRow]) = db.run(Sample ++= rows).map(_ => ())

  def deleteAll = db.run(Sample.delete).map(_ => ())

  def selectOpById(id: String, userId: Int) = db.run(Sample.
    filter(_.userId === userId).filter(_.id === id).result.head)

  def selectOpById(userId: Int, id: String) = db.run(Sample.
    filter(_.userId === userId).filter(_.id === id).result.headOption)

  def selectById(userId: Int, id: String) = db.run(Sample.
    filter(_.userId === userId).filter(_.id === id).result.head)

  def selectSiteInfoById(userId: Int, id: String) = db.run(Sample.
    filter(_.userId === userId).filter(_.id === id).map(x => (x.id, x.siteInfo)).result.head)

  def selectAll(userId: Int): Future[Seq[SampleRow]] = db.run(Sample.filter(_.userId === userId).sortBy(_.updateTime.desc).result)

  def selectAllSiteInfo(userId: Int) = db.run(Sample.filter(_.userId === userId).
    map(x => (x.id, x.siteInfo)).result)

  def selectAllSiteValue(userId: Int, siteName: String) = db.run(Sample.filter(_.userId === userId).
    map { x =>
      (x.siteInfo.+>(siteName))
    }.result)

  def fromSqlType(t: java.sql.Timestamp): DateTime =
    if (t == null) null else new DateTime(t.getTime)

  implicit val getTResult = GetResult(r => (r.nextString(), r.nextHStore()))
  implicit val getT1Result = GetResult(r => (r.nextString(), r.nextArray()))

  def selectStep(userId: Int, map: Map[String, String], mission: StepMissionRow) = {
    val stepNum = mission.stepNum
    val allKeys = map.keyList
    val emptyKeys = map.filter { case (k, v) =>
      v.isBlank
    }.keyList
    val nonEmptyMap = map -- emptyKeys
    val nonEmptyKeys = nonEmptyMap.keyList
    val nonEmptyMapSize = nonEmptyMap.size
    val nonEmptySiteNum = (nonEmptyMapSize * 0.7).toInt
    val nonEmptyEmptyMap = nonEmptyMap.keyList.map { k =>
      (k, "")
    }.toMap
    val emptyMap = map.keyList.map { k =>
      (k, "")
    }.toMap
    val diffNum = stepNum + 1

    db.run(Sample.filter(_.userId === userId).filter { x =>
      mission.containerName.map { ys =>
        x.containerName.inSetBind(ys)
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      val nonEmptyKeyMap = x.siteInfo.slice(nonEmptyKeys.bind)
      val nonEmptyMap = nonEmptyKeyMap.removeMap(nonEmptyEmptyMap.bind)
      val keyNum = nonEmptyMap.akeys.length()
      keyNum > nonEmptySiteNum
    }.filter { x =>
      val sameKeyMap = x.siteInfo.slice(allKeys.bind)
      val nonEmptyOtherMap = sameKeyMap -- (emptyKeys.bind)
      val nonEmptySelfMap = nonEmptyOtherMap.removeMap(emptyMap.bind)
      val diffMap = nonEmptySelfMap.removeMap(nonEmptyMap.bind)
      val dNum = diffMap.akeys.length()
      dNum < diffNum
    }.map(_.id).result.map(_.toList))

  }

  def test(userId: Int, map: Map[String, String], stepNum: Int) = {
    val allKeys = map.keyList
    val emptyKeys = map.filter { case (k, v) =>
      v.isBlank
    }.keyList

    val nonEmptyMap = map.filter { case (k, v) =>
      v.nonEmpty
    }
    val nonEmptyMapSize = nonEmptyMap.size

    val nonEmptyKeys = nonEmptyMap.keyList
    val nonEmptyEmptyMap = nonEmptyMap.keyList.map { k =>
      (k, "")
    }.toMap

    val emptyMap = map.keyList.map { k =>
      (k, "")
    }.toMap
    val diffNum = stepNum + 1
    val nonEmptySiteNum = (nonEmptyMapSize * 0.7).toInt

    db.run(Sample.filter(_.userId === userId).filter { x =>
      val nonEmptyKeyMap = x.siteInfo.slice(nonEmptyKeys.bind)
      val nonEmptyMap = nonEmptyKeyMap.removeMap(nonEmptyEmptyMap.bind)
      val keyNum = nonEmptyMap.akeys.length()
      keyNum > nonEmptySiteNum
    }.filter { x =>
      val sameKeyMap = x.siteInfo.slice(allKeys.bind)
      val nonEmptyOtherMap = sameKeyMap -- (emptyKeys.bind)
      val nonEmptySelfMap = nonEmptyOtherMap.removeMap(emptyMap.bind)
      val diffMap = nonEmptySelfMap.removeMap(nonEmptyMap.bind)
      val dNum = diffMap.akeys.length()
      dNum < diffNum
    }.map(_.id).result)

  }

  def selectAllId(userId: Int) = db.run(Sample.filter(_.userId === userId).map(_.id).result)

  def selectAllMissionName(userId: Int) = db.run(Sample.filter(_.userId === userId).
    map(_.updateMission).distinct.result)

  def selectAllContainerName(userId: Int) = db.run(Sample.filter(_.userId === userId).
    map(_.containerName).distinct.result)

  def selectAll(userId: Int, limit: Int, offset: Int) = db.run(Sample.
    filter(_.userId === userId).drop(offset).take(limit).result)

  def selectAll(userId: Int, ids: Seq[String]) = db.run(Sample.
    filter(_.userId === userId).filter(_.id.inSetBind(ids)).result)

  def selectAllNotSort(userId: Int, data: PageQueryData) = {
    val filterSamples = Sample.
      filter(_.userId === userId).filter { x =>
      data.gender.map(y => x.gender.inSetBind(y)).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.kind.map { y =>
        val ys = y.flatMap(VarTool.kindMap(_))
        x.kind.inSetBind(ys)
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.panel.map(y => x.panel.inSetBind(y)).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.id.map(y => x.id.like(s"%${y}%")).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.indexId.map(y => x.indexId.like(s"%${y}%")).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.sampleIntro.map { y =>
        x.sampleIntro.like(s"%${y}%")
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.comment.map(y => x.comment.like(s"%${y}%")).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.updateMission.map(y => x.updateMission.inSetBind(y)).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.startTime.map(y => x.updateTime >= y).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.endTime.map { y =>
        val curY = y.plusDays(1)
        x.updateTime <= curY
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.containerName.map(y => x.containerName.inSetBind(y)).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.singleSource.map(y => x.singleSource.inSetBind(y)).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.interlocusBalance.map(y => x.interlocusBalance.inSetBind(y)).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.autosomalLociTyped.map { y =>
        val levelMaps = y.map { t =>
          Map("level" -> t.replaceAll("风险", ""))
        }
        val bs = levelMaps.map { map =>
          x.autosomalLociTyped.@>(map)
        }
        val b = bs.reduceLeft((x, y) => x || y)
        b
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.yLociTyped.map { y =>
        val levelMaps = y.map { t =>
          Map("level" -> t.replaceAll("风险", ""))
        }
        val bs = levelMaps.map { map =>
          x.yLociTyped.@>(map)
        }
        val b = bs.reduceLeft((x, y) => x || y)
        b
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.xLociTyped.map { y =>
        val levelMaps = y.map { t =>
          Map("level" -> t.replaceAll("风险", ""))
        }
        val bs = levelMaps.map { map =>
          x.xLociTyped.@>(map)
        }
        val b = bs.reduceLeft((x, y) => x || y)
        b
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.isnpLociTyped.map { y =>
        val levelMaps = y.map { t =>
          Map("level" -> t.replaceAll("风险", ""))
        }
        val bs = levelMaps.map { map =>
          x.isnpLociTyped.@>(map)
        }
        val b = bs.reduceLeft((x, y) => x || y)
        b
      }.getOrElse(LiteralColumn(true))
    }
    val f = filterSamples.result
    db.run(f)
  }

  def selectAll(userId: Int, data: PageData) = {
    val filterSamples = Sample.
      filter(_.userId === userId).filter { x =>
      data.gender.map(y => x.gender.inSetBind(y)).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.kind.map { y =>
        val ys = y.flatMap(VarTool.kindMap(_))
        x.kind.inSetBind(ys)
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.panel.map(y => x.panel.toUpperCase.inSetBind(y)).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.id.map { y =>
        val ids = y.split("\\s+")
        val bs = ids.map { id =>
          x.id.like(s"%${id}%")
        }
        bs.reduceLeft((x, y) => x || y)
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.indexId.map(y => x.indexId.like(s"%${y}%")).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.sampleIntro.map { y =>
        x.sampleIntro.like(s"%${y}%")
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.comment.map(y => x.comment.like(s"%${y}%")).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.updateMission.map(y => x.updateMission.inSetBind(y)).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.startTime.map(y => x.updateTime >= y).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.endTime.map { y =>
        val curY = y.plusDays(1)
        x.updateTime <= curY
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.containerName.map(y => x.containerName.inSetBind(y)).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.singleSource.map(y => x.singleSource.inSetBind(y)).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.interlocusBalance.map(y => x.interlocusBalance.inSetBind(y)).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.autosomalLociTyped.map { y =>
        val levelMaps = y.map { t =>
          Map("level" -> t.replaceAll("风险", ""))
        }
        val bs = levelMaps.map { map =>
          x.autosomalLociTyped.@>(map)
        }
        val b = bs.reduceLeft((x, y) => x || y)
        b
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.yLociTyped.map { y =>
        val levelMaps = y.map { t =>
          Map("level" -> t.replaceAll("风险", ""))
        }
        val bs = levelMaps.map { map =>
          x.yLociTyped.@>(map)
        }
        val b = bs.reduceLeft((x, y) => x || y)
        b
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.xLociTyped.map { y =>
        val levelMaps = y.map { t =>
          Map("level" -> t.replaceAll("风险", ""))
        }
        val bs = levelMaps.map { map =>
          x.xLociTyped.@>(map)
        }
        val b = bs.reduceLeft((x, y) => x || y)
        b
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.isnpLociTyped.map { y =>
        val levelMaps = y.map { t =>
          Map("level" -> t.replaceAll("风险", ""))
        }
        val bs = levelMaps.map { map =>
          x.isnpLociTyped.@>(map)
        }
        val b = bs.reduceLeft((x, y) => x || y)
        b
      }.getOrElse(LiteralColumn(true))
    }
    val sortSamples = data.sort match {
      case None => filterSamples
      case Some(x) =>
        val order = data.order
        x match {
          case "id" =>
            order match {
              case "asc" => filterSamples.sortBy(_.id)
              case "desc" => filterSamples.sortBy(_.id.desc)
            }
          case "indexId" =>
            order match {
              case "asc" => filterSamples.sortBy(_.indexId)
              case "desc" => filterSamples.sortBy(_.indexId.desc)
            }
          case "sampleIntro" =>
            order match {
              case "asc" => filterSamples.sortBy(_.sampleIntro)
              case "desc" => filterSamples.sortBy(_.sampleIntro.desc)
            }
          case "updateMission" =>
            order match {
              case "asc" => filterSamples.sortBy(_.updateMission)
              case "desc" => filterSamples.sortBy(_.updateMission.desc)
            }
          case "updateTime" =>
            order match {
              case "asc" => filterSamples.sortBy(_.updateTime)
              case "desc" => filterSamples.sortBy(_.updateTime.desc)
            }
          case _ =>
            filterSamples
        }
    }
    val finalSamples = sortSamples
    val countF = finalSamples.length.result
    val resultF = finalSamples.drop(data.offset).take(data.limit).result
    val f = countF.zip(resultF)
    db.run(f)
  }

  def selectAll(userId: Int, data: PageData, sampleIds: List[String]) = {
    val filterSamples = Sample.
      filter(_.userId === userId).filter { x =>
      x.id.inSetBind(sampleIds)
    }.filter { x =>
      data.id.map(y => x.id.like(s"%${y}%")).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.indexId.map(y => x.indexId.like(s"%${y}%")).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.sampleIntro.map { y =>
        x.sampleIntro.like(s"%${y}%")
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.comment.map(y => x.comment.like(s"%${y}%")).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.updateMission.map(y => x.updateMission.inSetBind(y)).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.startTime.map(y => x.updateTime >= y).getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.endTime.map { y =>
        val curY = y.plusDays(1)
        x.updateTime <= curY
      }.getOrElse(LiteralColumn(true))
    }.filter { x =>
      data.containerName.map(y => x.containerName.inSetBind(y)).getOrElse(LiteralColumn(true))
    }
    val sortSamples = data.sort match {
      case None => filterSamples
      case Some(x) =>
        val order = data.order
        x match {
          case "id" =>
            order match {
              case "asc" => filterSamples.sortBy(_.id)
              case "desc" => filterSamples.sortBy(_.id.desc)
            }
          case "indexId" =>
            order match {
              case "asc" => filterSamples.sortBy(_.indexId)
              case "desc" => filterSamples.sortBy(_.indexId.desc)
            }
          case "sampleIntro" =>
            order match {
              case "asc" => filterSamples.sortBy(_.sampleIntro)
              case "desc" => filterSamples.sortBy(_.sampleIntro.desc)
            }
          case "updateMission" =>
            order match {
              case "asc" => filterSamples.sortBy(_.updateMission)
              case "desc" => filterSamples.sortBy(_.updateMission.desc)
            }
          case "updateTime" =>
            order match {
              case "asc" => filterSamples.sortBy(_.updateTime)
              case "desc" => filterSamples.sortBy(_.updateTime.desc)
            }
          case _ =>
            filterSamples
        }
    }
    val finalSamples = sortSamples
    val countF = finalSamples.length.result
    val resultF = finalSamples.drop(data.offset).take(data.limit).result
    val f = countF.zip(resultF)
    db.run(f)
  }


  def selectCount(userId: Int) = db.run(Sample.filter(_.userId === userId).length.result)

  def selectAll(userId: Int, missionName: String) = db.run(Sample.
    filter(_.userId === userId).filter(_.updateMission === missionName).sortBy(_.updateTime.desc).result)

  def selectAll(userId: Int, missionName: String, kind: String) = db.run(Sample.
    filter(_.userId === userId).filter(_.updateMission === missionName).filter(_.kind === kind).
    sortBy(_.updateTime.desc).result)

  def selectAllPos(userId: Int, missionName: String) = db.run(Sample.
    filter(_.userId === userId).filter(_.updateMission === missionName).
    filter { x =>
      val names = VarTool.posStrs
      names.map { name =>
        x.kind === name
      }.reduce((x, y) => x || y)
    }.sortBy(_.updateTime.desc).result)

  def selectAllNeg(userId: Int, missionName: String) = db.run(Sample.
    filter(_.userId === userId).filter(_.updateMission === missionName).filter { x =>
    val names = VarTool.negStrs
    names.map { name =>
      x.kind === name
    }.reduce((x, y) => x || y)
  }.sortBy(_.updateTime.desc).result)

  def selectAllNormal(userId: Int, missionName: String) = db.run(Sample.
    filter(_.userId === userId).filter(_.updateMission === missionName).filter { x =>
    val names = VarTool.normalStrs
    names.map { name =>
      x.kind === name
    }.reduce((x, y) => x || y)
  }.sortBy(_.updateTime.desc).result)

  def deleteById(userId: Int, id: String) = db.run(Sample.filter(_.userId === userId).filter(_.id === id).delete).map(_ => ())

  def deleteByIds(userId: Int, ids: List[String]) = db.run(Sample.filter(_.userId === userId).
    filter(_.id.inSetBind(ids)).delete).map(_ => ())

  def insertOrUpdates(rows: Seq[SampleRow]) = {
    val action = {
      val ids = rows.map(_.id)
      val delete = Sample.filter(_.id.inSetBind(ids)).delete
      val insertAll = Sample ++= rows
      delete.flatMap(_ => insertAll)
    }.transactionally
    db.run(action).map(_ => ())
  }


}
